//const sqlite3 = require('sqlite3').verbose();
const Database = require('better-sqlite3');
const formidable = require('formidable');
const jwt = require("jsonwebtoken");
var express = require('express');
// const multer = require('multer');
const moment = require('moment');
var cors = require('cors');
var md5 = require('md5');
const fs = require("fs");
var app = express()


const db_file = new Database('./DB/File.db');
const db_user = new Database('./DB/User.db');
const db_token = new Database('./DB/Token.db');
const db_logFailure = new Database('./DB/LogFailure.db');
if (db_file.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get('file') == undefined){
  db_file.prepare('CREATE TABLE file(originalname text, timestamp integer, file_uniquename text, username text, user_uniquename text, mimetype text, state text)').run();
}
if (db_user.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get('user') == undefined){
    db_user.prepare('CREATE TABLE user(username text, user_uniquename text, password text, manager integer)').run();
    db_user.prepare('INSERT INTO user(username, user_uniquename, password, manager) VALUES(?, ?, ?, ?)').run('admin', md5('admin'+Date.now()+Math.floor(Math.random() * 100)), ' ', 1);
}
if (db_token.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get('token') == undefined){
  db_token.prepare('CREATE TABLE token(token text, username text, user_uniquename text, manager integer, expirationTime integer)').run();
}
if (db_logFailure.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get('request') == undefined){
  db_logFailure.prepare('CREATE TABLE ip(ip text, lastActiveTimestamp integer, failureTimes integer)').run();
  db_logFailure.prepare('CREATE TABLE request(timestamp integer, ip text, request text)').run();
  db_logFailure.prepare('CREATE TABLE user(username text, failureTimes integer, lastFailureTimestamp integer, user_uniquename text)').run();
}


app.use(express.urlencoded({ extended: true }))
const port = 3001;
app.use(cors())
var BLACKLIST = []
var BLACKLIST_current = []

setInterval(()=>{
  BLACKLIST = []
  db_logFailure.prepare(`SELECT ip ip, lastActiveTimestamp lastActiveTimestamp, failureTimes failureTimes FROM ip ORDER BY failureTimes`).all().forEach(row => {
    if ( Date.now() - row.lastActiveTimestamp < 86400000 && row.failureTimes > 20 ){
      BLACKLIST.push(row.ip)
    }
  })
    //BLACKLIST = rows.map(({ip})=>ip)
  }, 60000)


var getClientIp = function(req) {
  var ipAddress = req.connection.remoteAddress;
  if (!ipAddress) { return ''; }
  if (ipAddress.substr(0, 7) == "::ffff:") { ipAddress = ipAddress.substr(7) }
  return "'"+ipAddress+"'";
};


function requestFailure(req, fields='', username=''){
  let ipAddress = getClientIp(req);
  let timestamp = Date.now()
  const get_failure_log = db_logFailure.prepare(`SELECT failureTimes failureTimes FROM ip WHERE ip = ?`).get(ipAddress)
  if (get_failure_log == undefined){
    db_logFailure.prepare(`INSERT INTO ip(ip, lastActiveTimestamp, failureTimes) VALUES(?, ?, 1)`).run(ipAddress, timestamp);
  } else {
    db_logFailure.prepare(`UPDATE ip SET failureTimes = ?, lastActiveTimestamp = ? WHERE ip = ?`).run(get_failure_log.failureTimes + 1, timestamp, ipAddress) }
  if (fields != ''){ db_logFailure.prepare(`INSERT INTO request(timestamp,ip,  request) VALUES(?, ?, ?)`).run(timestamp, ipAddress, fields) }
  if (username != '') {
    const get_failure_username = db_user.prepare(`SELECT user_uniquename user_uniquename, expirationTime FROM user WHERE username = ?`).run(username)
    if (get_failure_username != undefined){
      let { user_uniquename } = get_failure_username
      const get_failure_username_times = db_logFailure.prepare(`SELECT failureTimes failureTimes FROM user WHERE username = ?`).get(username)
      if (get_failure_username_times == undefined){
        db_logFailure.prepare(`INSERT INTO user(username, failureTimes, lastFailureTimestamp, user_uniquename) VALUES(?, 1, ?, ?)`).run(username, timestamp, user_uniquename)
      } else {
        db_logFailure.prepare(`UPDATE user SET failureTimes = ?, lastFailureTimestamp = ?, user_uniquename = ? WHERE username = ?`).run(get_failure_username_times.failureTimes + 1, timestamp, user_uniquename, username)
      }
    }
  }
}


app.use(function(req, res, next) {
  var ipAddress = getClientIp(req);
  if(BLACKLIST.indexOf(ipAddress) === -1){ next();
    } else { res.send('Host is down') }
  /*
  if (req.query.token != undefined){
    const getfrom_dbtoken = getfrom_dbtoken_.get(req.query.token)
      if (getfrom_dbtoken != undefined){
        if (getfrom_dbtoken.expirationTime < Date.now()){
          delfrom_dbtoken_.run(token)
          res.json({ needLogin: true })
        } else { next()
      }
    } else {
  }
  */
});

const getfrom_dbtoken_withtoken_ = db_token.prepare(`SELECT username username, user_uniquename user_uniquename, expirationTime expirationTime FROM token WHERE token = ?`)
const insertto_dbfile_ = db_file.prepare('INSERT INTO file(originalname, timestamp, file_uniquename, username, user_uniquename, mimetype, state) VALUES(?, ?, ?, ?, ?, ?, ?)')

app.post("/upload", (req, res) => {
  const form = formidable({});
  form.parse(req, (err, fields, files) => {

    for (const [key, value] of Object.entries(files)) {
      const getfrom_dbtoken_withtoken = getfrom_dbtoken_withtoken_.get(fields.token)
      if (getfrom_dbtoken_withtoken != undefined){
        let { username, user_uniquename } = getfrom_dbtoken_withtoken
        let timestamp = Date.now()
        let oldPath = value.filepath;
        let mimetype = value.mimetype;
        let file_uniquename = md5(timestamp + '.' + value.originalFilename)
        let newPath = `./Uploads/${file_uniquename}`;
        fs.rename(oldPath, newPath, (err) => {
          if (err) { return res.json({ status: "Failure", msg: "Failed to upload file. " + err.message, });}
          insertto_dbfile_.run(value.originalFilename, timestamp, file_uniquename, username, user_uniquename, mimetype, 'normal')
          res.json({
            file_uniquename: file_uniquename
          });
        });
      } else {
        res.json({
          file_uniquename: ""
        });
      }
    }
  });
});


app.post("/upload_del", (req, res) =>{
  const form = formidable({});
  form.parse(req, (err, fields, files) => {
    console.log(fields)
    db_file.prepare(`DELETE FROM file WHERE file_uniquename = ?`).run(fields.file_uniquename)
    fs.unlinkSync(`./Uploads/` + fields.file_uniquename);
    res.json({})
  })
})


const get_uniname_with_fileuniname_ = db_file.prepare(`SELECT originalname originalname, user_uniquename user_uniquename, mimetype mimetype FROM file WHERE file_uniquename = ?`)

app.get("/download", (req, res) =>{
  
  const row = get_uniname_with_fileuniname_.get(req.query.file_uniquename)
  if (row != undefined){
    const { originalname, mimetype } = row
    const filepath = `./Uploads/` + req.query.file_uniquename

    res.setHeader("Access-Control-Expose-Headers", "X-Suggested-Filename");
    res.setHeader('Content-Type', mimetype)
    res.setHeader("X-Suggested-Filename", originalname);
    res.setHeader('Content-Disposition', 'attachment; filename="' +  originalname + '"')

    fs.createReadStream(filepath).pipe(res)
    
  }
})

const getfrom_dbuser_with_username_ = db_user.prepare(`SELECT user_uniquename user_uniquename, password password, manager manager FROM user WHERE username  = ?`)
const insertto_token_ = db_token.prepare('INSERT INTO token(token, user_uniquename, username, manager, expirationTime) VALUES(?, ?, ?, ?, ?)')

app.post("/get_token", (req, res) => {
  const form = formidable({});
  form.parse(req, (err, fields, files) => {

    let { username, passwordmd5 } = fields;
    const getfrom_dbuser_with_username = getfrom_dbuser_with_username_.get(username)

      if (getfrom_dbuser_with_username != undefined){
        let {user_uniquename, password, manager} = getfrom_dbuser_with_username;
        if (md5(password) == passwordmd5){
          let token = md5(user_uniquename + Date.now() + Math.random())

          insertto_token_.run(token, user_uniquename, username, manager, moment().add(1, 'days').valueOf())

          
          res.json({ "token" : token });
        } else {
          requestFailure(req=req, username=username)
          res.json({ "token" : "" });
        }
      } else {
        requestFailure(req=req, username=username)
        res.json({
          "token" : ""
        });
      }
    });
})


const getfrom_dbtoken_ = db_token.prepare(`SELECT username username, user_uniquename user_uniquename, expirationTime expirationTime FROM token WHERE token = ?`)
const delfrom_dbtoken_ = db_token.prepare(`DELETE FROM token WHERE token = ?`)

app.post("/validate_token", (req, res) => {
  const form = formidable({});
  form.parse(req, (err, fields, files) => {

  let { token } = fields;
  const getfrom_dbtoken = getfrom_dbtoken_.get(token)
    if (getfrom_dbtoken != undefined){
      if (getfrom_dbtoken.expirationTime < Date.now()){
        delfrom_dbtoken_.run(token)
          res.json({
            "result" : false
          });
      } else {
        res.json({
          "result" : true
        });
      }
    } else {
      requestFailure(req=req, fields=fields)
      res.json({ "result" : false });
    }
  })
})


app.post("/register", (req, res) => {
  res.json({
  });
})


const get_listNotes_with_userUni_ = db_file.prepare(`SELECT originalname originalname, timestamp timestamp, file_uniquename file_uniquename, mimetype mimetype, state state FROM file WHERE user_uniquename = ?`)
const get_userUni_with_token_ = db_token.prepare(`SELECT user_uniquename user_uniquename FROM token WHERE token = ?`)

app.post("/list_notes", (req, res) => {
  const form = formidable({});
  form.parse(req, (err, fields, files) => {
    let { token, index, quantity, similarity } = fields

    const list = get_listNotes_with_userUni_.all(get_userUni_with_token_.get(token)['user_uniquename'])
    res.json({
      list: list
    });
  })
})


app.listen(port, () => {console.log(`App listening at :${port}`);});